﻿Imports Microsoft.Data.Sqlite
Public Class 字典管理
    Public proj, sort As String
    Public UID As Integer
    Public Sub QueryAndShowData()
        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                If ComboBox1.Text = "" Then
                    Dim strsql As String = "SELECT * FROM dictionary ORDER BY 更新时间"
                    Using cmd As New SqliteCommand(strsql, connection)
                        Using reader As SqliteDataReader = cmd.ExecuteReader()
                            Dim dataTable As New DataTable()
                            dataTable.Load(reader)
                            If dataTable.Rows.Count > 0 Then
                                DataGridView1.DataSource = dataTable
                            End If
                        End Using
                    End Using
                Else
                    Dim strsql As String = "SELECT * FROM dictionary where 项目=@proj ORDER BY 更新时间"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@proj", ComboBox1.Text)
                        Using reader As SqliteDataReader = cmd.ExecuteReader()
                            Dim dataTable As New DataTable()
                            dataTable.Load(reader)
                            If dataTable.Rows.Count > 0 Then
                                DataGridView1.DataSource = dataTable
                            End If
                        End Using
                    End Using
                End If
            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using

    End Sub
    Private Sub ConfigureGridViewColumns()
        With DataGridView1
            If DataGridView1 IsNot Nothing Then
                .Columns("选择").Width = 40
                .Columns("序号").Width = 40
                .Columns("分类").Width = 60
                .Columns("项目").Width = 60
                .Columns("更新时间").Width = 115
                .Columns("序号").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells  ' 包含标题行
            End If
        End With
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        QueryAndShowData()
        ConfigureGridViewColumns()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        proj = Trim(ComboBox1.Text)
        sort = Trim(TextBox1.Text)
        If sort = "" Or proj = "" Then
            MsgBox（"请完整填写内容"， vbOKOnly, "提示"） : Exit Sub
        Else
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "INSERT INTO dictionary (项目,分类,更新时间) VALUES(@proj, @sort,@updateTime)"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@proj", proj)
                        cmd.Parameters.AddWithValue("@sort", sort)
                        cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            QueryAndShowData()
            MsgBox（"新增记录成功"， vbOKOnly, "提示"）
        End If
    End Sub
    Private previousCheckedRow As Integer = -1
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.RowIndex < 0 OrElse e.ColumnIndex <> 0 Then Exit Sub
        If e.ColumnIndex = 0 AndAlso e.RowIndex >= 0 Then '假设复选框列为第0列
            If previousCheckedRow <> -1 AndAlso previousCheckedRow <> e.RowIndex Then
                DataGridView1.Rows(previousCheckedRow).Cells(0).Value = False
            End If
            DataGridView1.Rows(e.RowIndex).Cells(0).Value = Not DataGridView1.Rows(e.RowIndex).Cells(0).Value
            previousCheckedRow = e.RowIndex
        End If
        Try
            Dim currentRow = DataGridView1.Rows(e.RowIndex)
            UID = currentRow.Cells("序号").Value
            ComboBox1.Text = currentRow.Cells("项目").Value
            TextBox1.Text = currentRow.Cells("分类").Value
        Catch ex As Exception
            MessageBox.Show($"操作异常：{ex.Message}")
        End Try
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        proj = Trim(ComboBox1.Text)
        sort = Trim(TextBox1.Text)
        If proj = "" Or sort = "" Then
            MsgBox（"请完整填写内容"， vbOKOnly, "提示"） : Exit Sub
        Else
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "UPDATE dictionary SET 项目 = @proj,分类 = @sort,更新时间 = @updateTime WHERE 序号=@uid"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@uid", UID)
                        cmd.Parameters.AddWithValue("@proj", proj)
                        cmd.Parameters.AddWithValue("@sort", sort)
                        cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            ComboBox1.Text = ""
            QueryAndShowData()
            MsgBox（"修改记录成功"， vbOKOnly, "提示"）
        End If
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim result As DialogResult = MessageBox.Show("确定要删除这条记录吗？", "确认删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If result = DialogResult.Yes Then
            Using connection As New SqliteConnection(_connString)
                Try
                    connection.Open()
                    Dim strsql As String = "delete from dictionary WHERE 序号 = @uid"
                    Using cmd As New SqliteCommand(strsql, connection)
                        cmd.Parameters.AddWithValue("@uid", UID)
                        cmd.ExecuteNonQuery()
                    End Using
                Catch ex As Exception
                    Debug.WriteLine($"异常详情：{ex.ToString()}")
                    MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
            ComboBox1.Text = ""
            QueryAndShowData()
        End If
        MsgBox（"删除记录成功"， vbOKOnly, "提示"）
    End Sub
End Class